﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using System.Windows.Forms;

namespace DSS_Simplex
{
    class FileCmd
    {
        private static MySqlConnection connection;
        private static string ConnectionString = "Server=localhost;Database=dss;Uid=root;Pwd=;";
        private static DataGridView dgvToSave1, dgvToSave2, dgvToSave3;

        public static void TemporarySaveDGV(DataGridView dataGridView1, DataGridView dataGridView2, DataGridView dataGridView3)
        {
            dgvToSave1 = dataGridView1;
            dgvToSave2 = dataGridView2;
            dgvToSave3 = dataGridView3;
        }

        #region Save Project
        public static void SaveProject(string projectName)
        {
            try
            {
                connection = new MySqlConnection(ConnectionString);
                //sql command object
                string cmdText = "insert into project (nama_proj) VALUES (?nama_proj)";

                MySqlCommand cmd = new MySqlCommand(cmdText, connection);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("?nama_proj", MySqlDbType.VarChar, 200).Value = projectName;
                connection.Open();
                int result = cmd.ExecuteNonQuery();
                connection.Close();
                SaveAs(dgvToSave1, dgvToSave2, dgvToSave3);
                MessageBox.Show("Saved!!");

            }
            catch (Exception ex)
            {
                throw new Exception("Saving project error: "+ex.Message);
            }
        }
       
        public static void BindToDataSet(string cmdText, ref DataSet dataSet)
        {
            try
            {
                MySqlConnection connection = new MySqlConnection(ConnectionString);
                connection.Open();
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = cmdText;
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(dataSet);
            }
            catch (Exception ex)
            {
                throw new Exception("Binding to dataset error: " + ex.Message);
            }
        }
        public static void BindToDataTable(string cmdText, ref DataTable dataTable)
        {
            try
            {
                MySqlConnection connection = new MySqlConnection(ConnectionString);
                connection.Open();
                
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = cmdText;
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(dataTable);

                //connection.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Binding to dataset error: " + ex.Message);
            }
        }
               
        public static void SaveAs(DataGridView dataGridView1, DataGridView dataGridView2, DataGridView dataGridView4)
        {
            try
            {
                connection = new MySqlConnection(ConnectionString);
                //sql command object
                MySqlCommand cmd1 = connection.CreateCommand();
                connection.Open();
                cmd1.CommandText = "SELECT MAX(id_proj) FROM project";
                int id_project = ((int)cmd1.ExecuteScalar());
                Console.WriteLine(id_project);

                int i;
                int j;
                for (i = 0; i < dataGridView1.RowCount - 1; i++)
                {
                    string nama_var = Convert.ToString(dataGridView1.Rows[i].Cells[0].Value);
                    double koef_var = Convert.ToDouble(dataGridView1.Rows[i].Cells[1].Value);
                    double min_var = Convert.ToDouble(dataGridView1.Rows[i].Cells[2].Value);
                    double max_var = Convert.ToDouble(dataGridView1.Rows[i].Cells[3].Value);
                    string deskripsi = Convert.ToString(dataGridView1.Rows[i].Cells[4].Value);

                    //save variable
                    string cmdText = "insert into variable (nama_var,koef_var, min_var, max_var, deskripsi, id_proj) VALUES (@nama_var, @koef_var, @min_var, @max_var, @deskripsi, @id_proj)";
                    MySqlCommand cmd = new MySqlCommand(cmdText, connection);
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@nama_var", nama_var);
                    cmd.Parameters.AddWithValue("@koef_var", koef_var);
                    cmd.Parameters.AddWithValue("@min_var", min_var);
                    cmd.Parameters.AddWithValue("@max_var", max_var);
                    cmd.Parameters.AddWithValue("@deskripsi", deskripsi);
                    cmd.Parameters.AddWithValue("@id_proj", id_project);
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Variabel disimpan");
                }

                for (j = 0; j < dataGridView2.RowCount - 1; j++)
                {
                    string nama_const = Convert.ToString(dataGridView2.Rows[j].Cells[0].Value);
                    double min_const = Convert.ToDouble(dataGridView2.Rows[j].Cells[1].Value);
                    double max_const = Convert.ToDouble(dataGridView2.Rows[j].Cells[2].Value);
                    
                    //save constraint
                    string cmdText2 = "insert into `constraint` (nama_const, min_const, max_const, id_proj) VALUES (@nama_const, @min_const, @max_const, @id_proj)";
                    MySqlCommand cmd2 = new MySqlCommand(cmdText2, connection);
                    cmd2.CommandType = CommandType.Text;
                    cmd2.Parameters.AddWithValue("@nama_const", nama_const);
                    cmd2.Parameters.AddWithValue("@min_const", min_const); 
                    cmd2.Parameters.AddWithValue("@max_const", max_const);
                    cmd2.Parameters.AddWithValue("@id_proj", id_project);
                    cmd2.ExecuteNonQuery();
                    MessageBox.Show("constraint disimpan");
                }
                MessageBox.Show("id projet : " + id_project);
                        
                //save formula
                MySqlCommand cmdvar = connection.CreateCommand();
                cmdvar.CommandText = "SELECT id_var FROM `variable` WHERE id_proj = " + id_project + "";
                //int id_var = ((int)cmdvar.ExecuteScalar());
                MySqlDataReader reader = cmdvar.ExecuteReader();
                List<int> listvar = new List<int>();
                while (reader.Read())
                {
                    try
                    {
                        Console.WriteLine("" + reader);
                        listvar.Add(Convert.ToInt32(reader["id_var"].ToString()));
                    }
                    catch (Exception ex) { MessageBox.Show("errrrr " + ex.Message); }
                }
                reader.Close();

                MySqlCommand cmdconst = connection.CreateCommand();
                cmdconst.CommandText = "SELECT id_const FROM `constraint` WHERE id_proj = " + id_project + "";
                reader = cmdconst.ExecuteReader();
                List<int> listconst = new List<int>();
                while (reader.Read())
                {
                    try
                    {
                        //listconst.Add(int.Parse("" + reader));
                        listconst.Add(Convert.ToInt32(reader["id_const"].ToString()));
                    }
                    catch (Exception ex) { MessageBox.Show("ererererer " + ex.Message); }
                }

                reader.Close();
                Console.WriteLine("jumlah constraint:  " + listconst.Count);
                Console.WriteLine("jumlah variabel:  " + listvar.Count);
                //MessageBox.Show(dataGridView4.Rows[3].Cells[2].Value + "");
                for (i = 0; i < listconst.Count; i++)
                {
                    //MessageBox.Show("b");
                    for (j = 0; j < listvar.Count; j++) 
                    {
                        double value = Convert.ToDouble(dataGridView4.Rows[i].Cells[j + 1].Value);
                       
                        Console.WriteLine("value: " + value);
                        string cmdText3 = "insert into `formula` (id_var, id_const, value, id_proj) VALUES ("+listvar[j]+", "+listconst[i]+", @value, @id_proj)";
                        
                        MySqlCommand cmd3 = new MySqlCommand(cmdText3, connection);
                        cmd3.CommandType = CommandType.Text;

                        cmd3.Parameters.AddWithValue("@value", value);
                        cmd3.Parameters.AddWithValue("@id_proj", id_project);
                        cmd3.ExecuteNonQuery();
                        //MessageBox.Show("a");
                    }
                }
                connection.Close();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        #endregion

        #region Open Project
        public static void OpenProj(DataGridView dataGridView1, DataGridView dataGridView2, DataGridView dataGridView4, int listItem) 
        {
            
            //DataTable dt;
            
            DataTable dt1,dt2,dt3;
            try
            {
                int i,j;
                int jmlvar = 0;
                int jmlconst = 0;
                dt1 = new DataTable();
                dt2 = new DataTable();
                dt3 = new DataTable();

                FileCmd.BindToDataTable("SELECT nama_var, koef_var, min_var, max_var, deskripsi FROM `variable` where id_proj ="+listItem+"", ref dt1);
                dataGridView1.Columns[0].DataPropertyName = "nama_var";
                dataGridView1.Columns[1].DataPropertyName = "koef_var";
                dataGridView1.Columns[2].DataPropertyName = "min_var";
                dataGridView1.Columns[3].DataPropertyName = "max_var";
                dataGridView1.Columns[4].DataPropertyName = "deskripsi";
                dataGridView1.DataSource = dt1;
                    

                FileCmd.BindToDataTable("SELECT nama_const, min_const, max_const FROM `constraint` where id_proj = " + listItem + "", ref dt2);
                dataGridView2.Columns[0].DataPropertyName = "nama_const";
                dataGridView2.Columns[1].DataPropertyName = "min_const";
                dataGridView2.Columns[2].DataPropertyName = "max_const";
                dataGridView2.DataSource = dt2;

                FileCmd.CountVar(ref jmlvar,ref jmlconst, listItem);
                Console.WriteLine(jmlvar);
                Console.WriteLine(jmlconst);

                dataGridView4.ColumnCount = jmlvar + 1;
                for (j = 1; j < jmlvar + 1; j++)
                {
                    dataGridView4.Columns[j].Name = dataGridView1.Rows[j - 1].Cells[0].Value+"";
                }
                for (i = 0; i < jmlconst; i++)
                {
                    dataGridView4.Rows.Add(dataGridView2.Rows[i].Cells[0].Value+"");
                }
                MySqlConnection connection;
                string ConnectionString = "Server=localhost;Database=dss;Uid=root;Pwd=;";
                connection = new MySqlConnection(ConnectionString);
                MySqlCommand cmdfor = connection.CreateCommand();
                connection.Open();
                cmdfor.CommandText = "SELECT value FROM `formula` WHERE id_proj = " + listItem + "";
                MySqlDataReader reader = cmdfor.ExecuteReader();
                
                try
                {
                    for (i = 0; i < jmlconst; i++)
                    {

                        for (j = 1; j < jmlvar + 1; j++)
                        {
                            reader.Read();
                            //Console.WriteLine(Convert.ToDouble(reader["value"]));
                            dataGridView4.Rows[i].Cells[j].Value = Convert.ToDouble(reader["value"]);

                        }
                        Console.WriteLine(Convert.ToDouble(reader["value"]));
                    }
                }
                catch (Exception ex) { MessageBox.Show("errrrr " + ex.Message); }
                //}
                reader.Close();
                connection.Close();
                

            }
            catch (Exception ex)
            {
                MessageBox.Show(string.Format(ex.Message), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        public static void CountVar(ref int jmlVar, ref int jmlConst, int listItem)
        {
            connection = new MySqlConnection(ConnectionString);
            MySqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = "SELECT COUNT(id_var) FROM `variable` WHERE id_proj = " + listItem + "";
            connection.Open();
            jmlVar = Convert.ToInt32(cmd.ExecuteScalar());
            //Console.WriteLine(jmlVar);

            MySqlCommand cmd1 = connection.CreateCommand();
            cmd1.CommandText = "SELECT COUNT(id_const) FROM `constraint` WHERE id_proj = " + listItem + "";
            jmlConst = Convert.ToInt32(cmd1.ExecuteScalar());
            //Console.WriteLine(jmlConst);
        }

        #endregion
    }
}